﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using Entities;
using System.Data.SqlClient;
using DAOExceptions;

namespace SQLDao
{
    public class SQL_Role_DAO : IRoleDAO
    {
        public List<Role> GetRolesList()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.CommandText = "SELECT [id], [title], [privilege_level] FROM [Roles]";
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        var list = new List<Role>();
                        while (reader.Read())
                        {
                            list.Add(new Role((int)reader["id"], (string)reader["title"], (UserPrivilegeLevels)reader["privilege_level"]));
                        }
                        return list;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading roles list");
            }
        }

        public int AddRole(Role role)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.CommandText = "INSERT INTO [Roles] ([title], [privilege_level]) VALUES(@title, @privilege)";
            try
            {
                command.ExecuteNonQuery();
                command.Parameters.Add(new SqlParameter("@title", role.Title));
                command.Parameters.Add(new SqlParameter("@privilege", (int)role.RoleLevel));
                var res = command.ExecuteScalar().ToString();
                int id = -1;
                int.TryParse(res, out id);
                return id;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while adding role");
            }
        }

        public void DeleteRole(Role role)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.CommandText = "DELETE FROM [Roles] WHERE [id]=@id";
            command.Parameters.Add(new SqlParameter("@id", role.ID));
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while deleting role");
            }
        }

        public void ModifyRole(Role role)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.CommandText = "UPDATE [Roles] SET [title]=@title, [privilege_level]=@privilege WHERE [id]=@id";
            command.Parameters.Add(new SqlParameter("@title", role.Title));
            command.Parameters.Add(new SqlParameter("@privilege", (int)role.RoleLevel));
            command.Parameters.Add(new SqlParameter("@id", role.ID));
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while modifying role");
            }
        }

        public int GetRoleID(string title)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add(new SqlParameter("@title", title));
            command.CommandText = "SELECT [id] FROM [Roles] WHERE [title]=@title";
            try
            {
                var res = command.ExecuteScalar().ToString();
                int id = -1;
                int.TryParse(res, out id);
                return id;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading role id");
            }
        }

        public List<Role> GetRolesForUser(int user_id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add(new SqlParameter("@userid", user_id));
            command.CommandText = "SELECT [id], [title], [privilege_level] FROM [Roles] JOIN [RolesAssign] ON [RolesAssign].[role_id]=[Roles].[id] WHERE [RolesAssign].[user_id]=@userid";
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    var list = new List<Role>();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            list.Add(new Role((int)reader["id"], (string)reader["title"], (UserPrivilegeLevels)reader["privilege_level"]));
                        }
                    }
                    return list;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading roles list for user");
            }

        }

        public void RemoveRoleFromUser(int role_id, int user_id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add(new SqlParameter("@userid", user_id));
            command.Parameters.Add(new SqlParameter("@roleid", role_id));
            command.CommandText = "DELETE FROM [RolesAssign] WHERE [role_id]=@roleid AND [user_id]=@userid";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while removing role from user");
            }
        }

        public void AddRoleToUser(int role_id, int user_id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add(new SqlParameter("@userid", user_id));
            command.Parameters.Add(new SqlParameter("@roleid", role_id));
            command.CommandText = "INSERT INTO [RolesAssign] ([role_id], [user_id]) VALUES(@roleid, @userid)";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while adding role to user");
            }
        }
    }
}
